BEGIN;

CREATE SCHEMA IF NOT EXISTS staff;

CREATE TABLE IF NOT EXISTS staff.staffs
(
    id             BIGINT AUTO_INCREMENT NOT NULL,
    staffId        VARCHAR(10)           NOT NULL,
    createTime     TIMESTAMP             NOT NULL,
    lastUpdateTime TIMESTAMP             NOT NULL,
    name           VARCHAR(50)           NOT NULL,
    PRIMARY KEY (id)
);

CREATE SCHEMA IF NOT EXISTS duty;

CREATE TABLE IF NOT EXISTS duty.onDutyDef
(
    id        BIGINT AUTO_INCREMENT NOT NULL,
    name      VARCHAR(50)           NOT NULL,
    startTime TIME                  NOT NULL,
    endTime   TIME                  NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS duty.breakDef
(
    dutyId    BIGINT      NOT NULL,
    name      VARCHAR(10) NOT NULL,
    startTime TIME        NOT NULL,
    endTime   TIME        NOT NULL,
    PRIMARY KEY (dutyId, name)
);

CREATE TABLE IF NOT EXISTS duty.signrecords
(
    id         BIGINT AUTO_INCREMENT NOT NULL,
    signDate   DATE                  NOT NULL,
    signTime   TIME                  NOT NULL,
    recordType SMALLINT              NOT NULL, --1-上班,2-下班
    uid        VARCHAR(100)          NOT NULL,
    ipAddress  VARCHAR(40),
    machine    VARCHAR(100),
    userAgent  CLOB,
    PRIMARY KEY (id)
);

COMMIT;
INSERT INTO staff.staffs
    (staffId, name, createTime, lastUpdateTime)
VALUES ('00001', '白云鹏', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
       ('00002', '占欣伟', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
       ('00003', '朱正军', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
       ('00004', '陈龙', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
       ('00005', '陆仁江', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
       ('00006', '黄凯', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
       ('00007', '夏敏', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
       ('00008', '毛元润', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
       ('00009', '罗利民', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
       ('00010', '徐文俊', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
       ('00011', '贺广', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP),
       ('00012', '陈涛', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

INSERT INTO duty.onDutyDef
    (name, startTime, endTime)
VALUES ('普通班', '09:00:00', '18:30:00');

INSERT INTO duty.breakDef
    (dutyId, name, startTime, endTime)
VALUES (1, '午餐', '12:30:00', '14:00:00'),
       (1, '晚餐', '18:30:00', '19:30:00');

INSERT INTO duty.signrecords
    (signDate, signTime, recordType, uid, ipAddress, machine, userAgent)
VALUES (DATEADD('DAY', -2, CURRENT_DATE), '08:31:12', 1, '00001', '127.0.0.1', '127.0.0.1', 'IE8'),
       (DATEADD('DAY', -2, CURRENT_DATE), '18:34:23', 2, '00001', '127.0.0.1', '127.0.0.1', 'IE8'),
       (DATEADD('DAY', -2, CURRENT_DATE), '08:51:59', 1, '00002', '127.0.0.1', '127.0.0.1', 'IE8'),
       (DATEADD('DAY', -2, CURRENT_DATE), '18:42:59', 2, '00002', '127.0.0.1', '127.0.0.1', 'IE8'),
       (DATEADD('DAY', -2, CURRENT_DATE), '08:34:59', 1, '00003', '127.0.0.1', '127.0.0.1', 'IE8'),
       (DATEADD('DAY', -2, CURRENT_DATE), '21:21:59', 2, '00003', '127.0.0.1', '127.0.0.1', 'IE8'),
       (DATEADD('DAY', -1, CURRENT_DATE), '08:31:59', 1, '00001', '127.0.0.1', '127.0.0.1', 'IE8'),
       (DATEADD('DAY', -1, CURRENT_DATE), '17:31:59', 2, '00001', '127.0.0.1', '127.0.0.1', 'IE8'),
       (DATEADD('DAY', -1, CURRENT_DATE), '08:31:59', 1, '00002', '127.0.0.1', '127.0.0.1', 'IE8'),
       (DATEADD('DAY', -1, CURRENT_DATE), '08:39:59', 1, '00002', '127.0.0.1', '127.0.0.1', 'IE8'),
       (DATEADD('DAY', -1, CURRENT_DATE), '19:31:59', 2, '00002', '127.0.0.1', '127.0.0.1', 'IE8'),
       (DATEADD('DAY', 0, CURRENT_DATE), '08:34:59', 1, '00003', '127.0.0.1', '127.0.0.1', 'IE8'),
       (DATEADD('DAY', 0, CURRENT_DATE), '21:21:59', 2, '00003', '127.0.0.1', '127.0.0.1', 'IE8');

COMMIT;